package com.daxiong.dao;

import com.daxiong.common.Item;
import com.daxiong.model.ClientInfo;

import java.sql.*;
import java.sql.Connection;

/**
 * 数据访问对象
 *
 * @author sunyanxiong
 * @date 2017/4/29
 */
public class Dao {




    // 数据库连接相关
    private static String dbClassName = "com.mysql.jdbc.Driver";
    private static String dbUrl = "jdbc:mysql://localhost:3306/db_jxcmanager";
    private static String dbUser = "root";
    private static String dbPwd = "sunyanxiong";

    // 数据库连接对象
    private static Connection connection = null;
    private static PreparedStatement preStatement = null;
    private static ResultSet resultSet = null;


    static {
        if (null == connection) {
            try {
                Class.forName(dbClassName).newInstance();
                connection = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
                System.out.println("数据库连接成功!");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private Dao() {  // 禁止外部创建实例对象

    }

    /**
     * 根据 id/name 获取客户信息
     */
    public static ClientInfo getClientInfo(Item item) {
        String sql = "";
        ClientInfo clientInfo = new ClientInfo();
        if (null != item.getId()) {
            sql = "SELECT * FROM tb_clientinfo WHERE id= ? ";
            resultSet = getResultSet(sql, item.getId());
        } else {
            sql = "SELECT * FROM tb_clientinfo WHERE name= ? ";
            resultSet = getResultSet(sql, item.getName());
        }

        try {
            if (resultSet.next()) {   // 封装数据
                clientInfo.setId(resultSet.getString("id").trim());
                clientInfo.setName(resultSet.getString("name").trim());
                clientInfo.setShortName(resultSet.getString("shortName").trim());
                clientInfo.setAddress(resultSet.getString("address").trim());
                clientInfo.setPostcode(resultSet.getString("postcode").trim());
                clientInfo.setTelphone(resultSet.getString("telphone").trim());
                clientInfo.setFax(resultSet.getString("fax").trim());
                clientInfo.setCantact(resultSet.getString("cantact").trim());
                clientInfo.setCantactPhone(resultSet.getString("cantactPhone").trim());
                clientInfo.setEmail(resultSet.getString("email").trim());
                clientInfo.setBank(resultSet.getString("bank").trim());
                clientInfo.setBankAccount(resultSet.getString("bankAccount").trim());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection();
        }

        return clientInfo;
    }

    /**
     * 访问数据库公共方法
     */
    public static ResultSet getResultSet(String sql, String... str) {
        try {
            preStatement = connection.prepareStatement(sql);
            for (int i = 0; i < str.length; i++) {
                preStatement.setString(i + 1, str[i]);
            }
            resultSet = preStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    /**
     * 关闭数据库连接
     */
    public static void closeConnection() {
        if (null != resultSet) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (null != preStatement) {
            try {
                preStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (null != connection) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
